package JDBC;

import java.sql.*;
import java.text.SimpleDateFormat;

import method.*;

import static JDBC.KCYDBCPUtil.getConnection;

/**
 * @author 顾书豪
 */
public class DBUtils {

    //数据库连接
    public static Connection getConnection() {
        return KCYDBCPUtil.getConnection();

    }


    /**
     * @Description: 验证登录
     * @Param: [phoneNumber, password]
     * @return: String
     */
    public static String check(String phoneNumber, String password) {

        Connection con = getConnection();
        String sql = String.format("select count(*) from account_information where PhoneNumber='%s'", phoneNumber);
        try {
            Statement stmt = con.createStatement();
            int count;
            ResultSet rs = stmt.executeQuery(sql);
            rs.next();
            count = rs.getInt(1);

            if (count == 1) {
                sql = String.format("SELECT Password FROM account_information where PhoneNumber='%s'", phoneNumber);
                try {
                    stmt = con.createStatement();
                    rs = stmt.executeQuery(sql);
                    rs.next();
                    String Password = rs.getString("Password");
                    if (Password.equals(password))
                        return "true";
                    else return "false";
                } catch (Exception se) {
                    se.printStackTrace();
                }
                return "false";
            } else {
                return "not exist";
            }
        } catch (Exception se) {
            se.printStackTrace();
            return "false";
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

    /**
     * @Description: 注册
     * @Param: [phoneNumber, password，accountName， name]
     * @return: String
     */
    public static String register(String phoneNumber, String password, String accountName, String name) {
        return register(phoneNumber, password, accountName, name, 0);
    }

    /**
     * @Description: 注册
     * @Param: [phoneNumber, password，accountName， name]
     * @return: String
     */
    public static String register(String phoneNumber, String password, String accountName, String name, int deposit) {
        Connection conn = getConnection();
        String sql = String.format("select count(*) from account_information where PhoneNumber='%s'", phoneNumber);
        try {
            Statement stmt = conn.createStatement();
            int count;
            ResultSet rs = stmt.executeQuery(sql);
            rs.next();
            count = rs.getInt(1);

            System.out.printf("count: %d\n", count);
            if (count == 0) {
                sql = String.format("insert into account_information (`PhoneNumber`,`RealName`,`AccountName`,`Password`,`Deposit`) values ('%s','%s','%s','%s',%d);", phoneNumber, name, accountName, password, deposit);
                int updateValue = stmt.executeUpdate(sql);
                if (updateValue == 1) {
                    rs.close();
                    stmt.close();
                    conn.close();
                    return "true";
                } else {
                    rs.close();
                    stmt.close();
                    conn.close();
                    return "false";
                }
            } else {
                rs.close();
                stmt.close();
                conn.close();
                return "exist";
            }

        } catch (Exception se) {
            se.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        return "false";
    }

    /**
     * @Description: 修改密码
     * @Param: [PhoneNumber, NewPassword]
     * @return: String
     */
    public static boolean ChangeCode(String PhoneNumber, String NewPassword) {
        Connection conn = getConnection();

        try {
            Statement stmt = conn.createStatement();
            String sql = String.format("update `account_information` SET `Password` = '%s' where `PhoneNumber` = '%s'", NewPassword, PhoneNumber);
            System.out.println(sql);
            int updateValue = stmt.executeUpdate(sql);
            if (updateValue == 1) {
                stmt.close();
                conn.close();
                return true;
            } else {
                stmt.close();
                conn.close();
                return false;
            }

        } catch (Exception se) {
            se.printStackTrace();
        } finally {
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        return false;
    }

    /**
     * @Description: 寻找用户名
     * @Param: [PhoneNumber]
     * @return: String
     */
    public static String FindName(String PhoneNumber) {
        Connection conn = getConnection();
        String accountName = "";
        try {
            Statement stmt = conn.createStatement();
            String sql = String.format("SELECT AccountName FROM account_information where PhoneNumber= '%s'", PhoneNumber);
            ResultSet rs = stmt.executeQuery(sql);
            rs.next();
            accountName = rs.getString("AccountName");
            stmt.close();
            conn.close();
        } catch (Exception se) {
            se.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        return accountName;
    }


    /**
     * @Description: 用来向数据库中增加商品信息
     * @Param: [itemID, itemName, itemPrice, itemType, itemDescription, itemQuantity]
     * @return: boolean
     */

    //这里照我新的接口文档给出的来进行更改，也是改成返回string
    public static String addItem(String itemID, String itemName, double itemPrice, String itemType,
                                 String itemDescription, int itemQuantity) {

        Connection con = getConnection();
//        String sql = "insert into item_information values(" + itemID + "," + "\"" + itemName + "\"" + "," +
//                itemPrice + "," + "\"" + itemType + "\"" + "," + "\"" + itemDescription + "\"" + "," + itemQuantity + ");";
        String sql = String.format("insert into item_information (ItemID,ItemName,ItemPrice,ItemType,ItemDescription,itemQuantity) values('%s','%s',%f,'%s','%s',%d)", itemID, itemName, itemPrice, itemType, itemDescription, itemQuantity);
        System.out.println(sql);
        try {
            Statement sta = con.createStatement();
            sta.executeUpdate(sql);
            con.close();
            return "true";

        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return "false";
    }


    /**
     * @itemId: 要查询的商品id
     */
    public static int getItemBuyQuantity(String phoneNumber, String itemId,String Time) {
        Connection con = getConnection();
        String sql = String.format("select ItemQuantity from account_buy_information where `ItemId` ='%s' and `PhoneNumber`='%s' and `CreateTime`='%s'", itemId, phoneNumber,Time);
        try {
            Statement sta = con.createStatement();
            ResultSet res = sta.executeQuery(sql);
            res.next();
            return res.getInt(1);
        } catch (SQLException throwables) {
            return 0;
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

    public static boolean addDeposit(String phoneNumber, double num) {
        AccountInformation accountInformation = QueryAccountInformation(phoneNumber);
        if (accountInformation == null) {
            return false;
        }
        double deposit = accountInformation.getDeposit();
        if (num + deposit < 0) {
            return false;
        }
        Connection con = getConnection();
        try {
            String sql = String.format("update account_information set `Deposit` = %f where `PhoneNumber` = '%s'", deposit + num, phoneNumber);
            System.out.println(sql);
            System.out.println(deposit);
            Statement statement = con.createStatement();
            int affected = statement.executeUpdate(sql);
            if (affected == 0) {
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {

            }
        }
        return true;
    }

    public static boolean refund(String phoneNumber, String itemId,long mill) {
        ItemInformation[] items = QueryItemInformation("ItemId", itemId);
        Timestamp time=new Timestamp(mill);
        String Time= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(time);
        if (items.length == 0) {
            return false;
        }
        Double itemPrice = items[0].getItemPrice();
        int quantityOld = items[0].getItemQuantity();
        int quantity = getItemBuyQuantity(phoneNumber, itemId,Time);
        Double num = itemPrice * quantity;
        String sql = String.format("delete from account_buy_information where `ItemId` = '%s' and `PhoneNumber` ='%s' and `CreateTime`='%s'", itemId, phoneNumber,Time);
        String sql2 = String.format("update item_information where `ItemId`= '%s' set `ItemQuantity`= %d",itemId,quantity+quantityOld);
        System.out.println(sql);
        Connection con = getConnection();
        try {
            Statement statement = con.createStatement();
            int affected = statement.executeUpdate(sql);
            if (affected == 0) {
                return false;
            }
            affected = statement.executeUpdate(sql2);
            if(affected==0){
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return addDeposit(phoneNumber, num);

    }


    /**
     * @Description: 添加商品图片
     * @Param: [itemId, picture]
     * @return: String true为增加成功 false为增加失败
     */
    public static String addItemPicture(String itemId, String picture) {

        Connection con = getConnection();
//        String sql = "insert into item_picture values(" + "\"" + itemId + "\"" + "," + "\"" + picture + "\");";
        String sql = String.format("insert into item_picture values('%s','%s')", itemId, picture);
        System.out.println(sql);
        try {
            Statement sta = con.createStatement();
            sta.executeUpdate(sql);
            return "true";

        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return "false";

    }

    /**
     * @Description: 添加顾客购买信息
     * @Param: [phoneNumber, itemId, itemQuantity]
     * @return: String true为购买成功 false为购买失败 lakeMoney为缺少钱
     */
    public static String addBuyInformation(String phoneNumber, String itemId, int itemQuantity) {
        Connection con = getConnection();
//        String sql = "insert into account_buy_information values(" + "\"" + phoneNumber + "\"" + ","
//                + "\"" + itemId + "\"" + "," + itemQuantity + ");";
        String sql = String.format("insert into account_buy_information (PhoneNumber,ItemID,ItemQuantity) values ('%s','%s',%d);", phoneNumber, itemId, itemQuantity);
        System.out.println(sql);
        if (QueryItemInformation("ItemId", itemId)[0].getItemPrice()*itemQuantity
                <= QueryAccountInformation("PhoneNumber", phoneNumber)[0].getDeposit()) {
            try {
                Statement sta = con.createStatement();
                sta.executeUpdate(sql);
                //减少商品数量
                updateData("item_information", "ItemID", itemId, "ItemQuantity",
                        QueryItemInformation("ItemID", itemId)[0].getItemQuantity() - itemQuantity);
                //减少用户存款
                updateData("account_information", "PhoneNumber", phoneNumber, "Deposit",
                        QueryAccountInformation("PhoneNumber", phoneNumber)[0].getDeposit() - QueryItemInformation("ItemId", itemId)[0].getItemPrice()*itemQuantity);

                return "true";

            } catch (SQLException throwable) {
                throwable.printStackTrace();
            } finally {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
        } else  {
            return "lakeMoney";
        }

        return "false";
    }

    /**
     * @Description: 添加商家发布信息
     * @Param: [phoneNumber, itemId, itemQuantity]
     * @return: boolean
     */
    public static String addReleaseInformation(String phoneNumber, String itemId, int itemQuantity) {

        Connection con = getConnection();
//        String sql = "insert into account_release_information values(" + "\"" + phoneNumber + "\"" + ","
//                + "\"" + itemId + "\"" + "," + itemQuantity + ");";
        String sql = String.format("insert into account_release_information values('%s','%s');", phoneNumber, itemId);
        System.out.println(sql);
        try {
            Statement sta = con.createStatement();
            sta.executeUpdate(sql);
            return "true";

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return "false";

    }

    /**
     * @Description: 添加顾客收藏商品信息
     * @Param: [phoneNumber, itemID]
     * @return: boolean
     */
    public static String addItemCollect(String phoneNumber, String itemID) {
        Connection con = getConnection();
//        String sql = "insert into item_collect values(" + "\"" + phoneNumber + "\"" + "," + "\"" + itemID + "\");";
        String sql = String.format("insert into item_collect values('%s','%s');", phoneNumber, itemID);
        System.out.println(sql);
        try {
            Statement sta = con.createStatement();
            sta.executeUpdate(sql);
            return "true";

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return "false";
    }


    /**
     * @param tableName 表名
     * @param dataName  数据名
     * @param data      将要删除的数据
     * @return
     * @Description 删除对应表中的数据
     */
    public static boolean deleteData(String tableName, String dataName, String data) {

        Connection con = getConnection();
//        String sql = "DELETE FROM" + tableName + "WHERE" + dataName + "=" + "\"" + data + "\"" + ";";
        String sql = String.format("DELETE FROM `%s` where `%s` = '%s';", tableName, dataName, data);
        System.out.println(sql);
        try {
            Statement sta = con.createStatement();
            sta.executeUpdate(sql);
            /*if ( tableName == "account_information"){
                deleteData("account_buy_information", "PhoneNumber ",data);
                deleteData("account_release_information", "PhoneNumber ",data);
            }*/
            return true;

        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return false;
    }

    public static boolean deleteData(String tableName, String dataName, int data) {

        Connection con = getConnection();
//        String sql = "DELETE FROM" + tableName + "WHERE" + dataName + "=" + data + ";";
        String sql = String.format("DELETE FROM `%s` where `%s` = %d ;", tableName, dataName, data);
        System.out.println(sql);
        try {
            Statement sta = con.createStatement();
            sta.executeUpdate(sql);

            return true;

        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return false;
    }


    /**
     * @param tableName       需要改变的表中的数据
     * @param dataName        查询的数据名
     * @param data            查询的数据
     * @param changedDataName 改变的数据名
     * @param changedData     改变的数据
     * @return 反回对应的对象数组
     * @Description: 改变对应表中的数据
     */
    //这个设计nice
    public static boolean updateData(String tableName, String dataName, int data,
                                     String changedDataName, String changedData) {

        Connection con = getConnection();

//        String sql = "update " + tableName + " set " + changedDataName + "=" + "\"" + changedData + "\"" +
//                " where " + dataName + "=" + data + ";";

        String sql = String.format("update `%s` set `%s` = '%s' where `%s` = %d", tableName, changedDataName, changedData, dataName, data);
        System.out.println(sql);

        try {
            Statement sta = con.createStatement();
            sta.executeUpdate(sql);
            return true;

        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return false;
    }

    public static boolean updateData(String tableName, String dataName, int data,
                                     String changedDataName, int changedData) {

        Connection con = getConnection();

//        String sql = "update " + tableName + " set " + changedDataName + "=" + changedData +
//                " where " + dataName + "=" + data + ";";
        String sql = String.format("update `%s` set `%s` = %d where `%s` = %d", tableName, changedDataName, changedData, dataName, data);
        System.out.println(sql);

        try {
            Statement sta = con.createStatement();
            sta.executeUpdate(sql);
            return true;

        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return false;
    }

    public static boolean updateData(String tableName, String dataName, int data,
                                     String changedDataName, double changedData) {

        Connection con = getConnection();

//        String sql = "update " + tableName + " set " + changedDataName + "=" + changedData +
//                " where " + dataName + "=" + data + ";";
        String sql = String.format("update `%s` set `%s` = %f where `%s` = %d", tableName, changedDataName, changedData, dataName, data);
        System.out.println(sql);

        try {
            Statement sta = con.createStatement();
            sta.executeUpdate(sql);
            return true;

        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return false;
    }

    public static boolean updateData(String tableName, String dataName, String data,
                                     String changedDataName, String changedData) {

        Connection con = getConnection();

//        String sql = "update " + tableName + " set " + changedDataName + "=" + "\"" + changedData + "\"" +
//                " where " + dataName + "=" + "\"" + data + "\"" + ";";

        String sql = String.format("update `%s` set `%s` = '%s' where `%s` = '%s'", tableName, changedDataName, changedData, dataName, data);

        System.out.println(sql);

        try {
            Statement sta = con.createStatement();
            sta.executeUpdate(sql);
            return true;

        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return false;
    }

    public static boolean updateData(String tableName, String dataName, String data,
                                     String changedDataName, int changedData) {

        Connection con = getConnection();

//        String sql = "update " + tableName + " set " + changedDataName + "=" + changedData +
//                " where " + dataName + "=" + "\"" + data + "\"" + ";";

        String sql = String.format("update `%s` set `%s` = %d where `%s` = '%s'", tableName, changedDataName, changedData, dataName, data);

        System.out.println(sql);

        try {
            Statement sta = con.createStatement();
            sta.executeUpdate(sql);
            return true;

        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return false;
    }

    public static boolean updateData(String tableName, String dataName, String data,
                                     String changedDataName, double changedData) {

        Connection con = getConnection();

//        String sql = "update " + tableName + " set " + changedDataName + "=" + changedData +
//                " where " + dataName + "=" + "\"" + data + "\"" + ";";

        String sql = String.format("update `%s` set `%s` = %f where `%s` = '%s'", tableName, changedDataName, changedData, dataName, data);

        System.out.println(sql);

        try {
            Statement sta = con.createStatement();
            sta.executeUpdate(sql);
            return true;

        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return false;
    }


    /**
     * 查询数据
     *
     * @param dataName 查询的数据名
     * @param data     查询的数据
     */
    //这个是查询账号信息吗？
    public static AccountInformation[] QueryAccountInformation(String dataName, String data) {

        Connection con = getConnection();

//        String sql = "select * from account_information where " + dataName + "=" + "\"" + data + "\";";

        String sql = String.format("select * from account_information where `%s` = '%s'", dataName, data);
        System.out.println(sql);

        try {
            Statement sta = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = sta.executeQuery(sql);

            rs.last();
            int count = rs.getRow();
            rs.beforeFirst();
            AccountInformation[] accountIf = new AccountInformation[count];

            int i = 0;
            while (rs.next()) {
                accountIf[i] = new AccountInformation();
                accountIf[i].setAccountName(rs.getString("AccountName"));
                accountIf[i].setPassword(rs.getString("Password"));
                accountIf[i].setPhoneNumber(rs.getString("PhoneNumber"));
                accountIf[i].setRealName(rs.getString("RealName"));
                accountIf[i].setDeposit(rs.getDouble("Deposit"));
                accountIf[i].setCreateTime(rs.getTimestamp("CreateTime"));
                i++;
            }

            System.out.println("" + accountIf[0].getAccountName() + accountIf[0].getDeposit() +
                    accountIf[0].getPassword() + accountIf[0].getPhoneNumber() + accountIf[0].getRealName());

            return accountIf;
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return new AccountInformation[0];
    }

    //这里应该是直接传参PhoneNumber就行，返回所有用户已经购买的情况
    public static AccountBuyInformation[] QueryAccountBuyInformation(String PhoneNumber) {

        Connection con = getConnection();

//        String sql = "select * from account_buy_information where " + dataName + "=" + "\"" + data + "\";";
        String sql = String.format("select * from account_buy_information where `%s` = '%s'","PhoneNumber",PhoneNumber);
        System.out.println(sql);

        try {
            Statement sta = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = sta.executeQuery(sql);

            rs.last();
            int count = rs.getRow();
            rs.beforeFirst();
            AccountBuyInformation[] accountBuyIf = new AccountBuyInformation[count];

            int i = 0;
            while (rs.next()) {
                accountBuyIf[i] = new AccountBuyInformation();
                accountBuyIf[i].setItemID(rs.getString("ItemID"));
                accountBuyIf[i].setPhoneNumber(rs.getString("PhoneNumber"));
                accountBuyIf[i].setItemQuantity(rs.getInt("ItemQuantity"));
                accountBuyIf[i].setCreateTime(rs.getTimestamp("CreateTime"));
                i++;
            }

            return accountBuyIf;
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return new AccountBuyInformation[0];
    }
    public static AccountBuyInformation[] QueryAccountBuyOneInformation(String ItemID,String PhoneNumber,long mill) {

        Connection con = getConnection();
        Timestamp time=new Timestamp(mill);
        String Time= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(time);

//        String sql = "select * from account_buy_information where " + dataName + "=" + "\"" + data + "\";";
        String sql = String.format("select * from account_buy_information where `%s` = '%s' and `%s`='%s' and `CreateTime`='%s'","PhoneNumber",PhoneNumber,"ItemID",ItemID,Time);
        System.out.println(sql);

        try {
            Statement sta = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = sta.executeQuery(sql);

            rs.last();
            int count = rs.getRow();
            rs.beforeFirst();
            AccountBuyInformation[] accountBuyIf = new AccountBuyInformation[count];

            int i = 0;
            while (rs.next()) {
                accountBuyIf[i] = new AccountBuyInformation();
                accountBuyIf[i].setItemID(rs.getString("ItemID"));
                accountBuyIf[i].setPhoneNumber(rs.getString("PhoneNumber"));
                accountBuyIf[i].setItemQuantity(rs.getInt("ItemQuantity"));
                accountBuyIf[i].setCreateTime(rs.getTimestamp("CreateTime"));
                i++;
            }

            return accountBuyIf;
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return new AccountBuyInformation[0];
    }

    //这个方法应该也是传参PhoneNumber，返回所有的该账号发布的信息
    public static AccountReleaseInformation[] QueryAccountReleaseInformation(String dataName, String data) {

        Connection con = getConnection();

//        String sql = "select * from account_release_information where " + dataName + "=" + "\"" + data + "\";";
        String sql = String.format("select * from account_release_information where `%s` = '%s'", dataName, data);

        System.out.println(sql);

        try {
            Statement sta = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = sta.executeQuery(sql);

            rs.last();
            int count = rs.getRow();
            rs.beforeFirst();
            AccountReleaseInformation[] accountRelIf = new AccountReleaseInformation[count];

            int i = 0;
            while (rs.next()) {
                accountRelIf[i] = new AccountReleaseInformation();
                accountRelIf[i].setPhoneNumber(rs.getString("PhoneNumber"));
                accountRelIf[i].setItemID(rs.getString("ItemID"));
                //accountRelIf[i].setCreateTime(rs.getDate("CreateTime"));
                i++;
            }

            return accountRelIf;
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return new AccountReleaseInformation[0];
    }

    //应该也是传参PhoneNumber，返回所有的收藏
    public static ItemCollect[] QueryItemCollect(String dataName, String data) {

        Connection con = getConnection();

//        String sql = "select * from item_collect where " + dataName + "=" + "\"" + data + "\";";

        String sql = String.format("select * from item_collect where `%s` = '%s'", dataName, data);

        System.out.println(sql);

        try {
            Statement sta = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = sta.executeQuery(sql);

            rs.last();
            int count = rs.getRow();
            rs.beforeFirst();
            ItemCollect[] itemCollect = new ItemCollect[count];

            int i = 0;
            while (rs.next()) {
                itemCollect[i] = new ItemCollect();
                itemCollect[i].setItemID(rs.getString("ItemID"));
                itemCollect[i].setPhoneNumber(rs.getString("PhoneNumber"));
                //itemCollect[i].setCreateTime(rs.getDate("CreateTime"));
                i++;
            }

            return itemCollect;
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return new ItemCollect[0];
    }

    //此处应该重写三次这个方法，分别是通过ItemID，ItemType和ItemName来进行搜索，并且ItemName应该支持匹配
    public static ItemInformation[] QueryItemInformation(String dataName, String data) {

        Connection con = getConnection();

//        String sql = "select * from item_information where " + dataName + "=" + "\"" + data + "\";";

        String sql = String.format("select * from item_information where `%s` = '%s'", dataName, data);


        System.out.println(sql);

        try {
            Statement sta = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = sta.executeQuery(sql);

            rs.last();
            int count = rs.getRow();
            rs.beforeFirst();
            ItemInformation[] itemInformation = new ItemInformation[count];

            int i = 0;
            while (rs.next()) {
                itemInformation[i] = new ItemInformation();
                itemInformation[i].setItemID(rs.getString("ItemID"));
                itemInformation[i].setItemName(rs.getString("ItemName"));
                itemInformation[i].setItemQuantity(rs.getInt("ItemQuantity"));
                itemInformation[i].setItemPrice(rs.getDouble("ItemPrice"));
                itemInformation[i].setItemType(rs.getString("ItemType"));
                itemInformation[i].setItemDescription(rs.getString("ItemDescription"));
                itemInformation[i].setCreateTime(rs.getTimestamp("CreateTime"));
                i++;
            }
            return itemInformation;
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return new ItemInformation[0];
    }
    public static ItemInformation[] QueryItemInformation(String[] data,int ItemType) {

        Connection con = getConnection();

//        String sql = "select * from item_information where " + dataName + "=" + "\"" + data + "\";";
        String sql=null;
        if(ItemType==1) {
            sql = String.format("select * from item_information where `%s` = '%s'", "ItemType", data[0]);
        }else if(ItemType==2){
            sql = String.format("select * from item_information where `%s` = '%s' or `%s` = '%s'", "ItemType", data[0],"ItemType",data[1]);
        }else if(ItemType==3){
            sql = String.format("select * from item_information where `%s` = '%s' or `%s` = '%s' or `%s` = '%s'", "ItemType", data[0],"ItemType",data[1],"ItemType",data[2]);
        }else if(ItemType==4){
            sql = String.format("select * from item_information where `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s'", "ItemType", data[0],"ItemType",data[1],"ItemType",data[2],"ItemType",data[3]);
        }else if(ItemType==5){
            sql = String.format("select * from item_information where `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s'", "ItemType", data[0],"ItemType",data[1],"ItemType",data[2],"ItemType",data[3],"ItemType",data[4]);
        }else if(ItemType==6){
            sql = String.format("select * from item_information where `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s'", "ItemType", data[0],"ItemType",data[1],"ItemType",data[2],"ItemType",data[3],"ItemType",data[4],"ItemType",data[5]);
        }else if(ItemType==7){
            sql = String.format("select * from item_information where `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s'", "ItemType", data[0],"ItemType",data[1],"ItemType",data[2],"ItemType",data[3],"ItemType",data[4],"ItemType",data[5],"ItemType",data[6]);
        }else if(ItemType==8){
            sql = String.format("select * from item_information where `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s'", "ItemType", data[0],"ItemType",data[1],"ItemType",data[2],"ItemType",data[3],"ItemType",data[4],"ItemType",data[5],"ItemType",data[6],"ItemType",data[7]);
        }else if(ItemType==9){
            sql = String.format("select * from item_information where `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s'", "ItemType", data[0],"ItemType",data[1],"ItemType",data[2],"ItemType",data[3],"ItemType",data[4],"ItemType",data[5],"ItemType",data[6],"ItemType",data[7],"ItemType",data[8]);
        }else if(ItemType==10){
            sql = String.format("select * from item_information where `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s' or `%s` = '%s'", "ItemType", data[0],"ItemType",data[1],"ItemType",data[2],"ItemType",data[3],"ItemType",data[4],"ItemType",data[5],"ItemType",data[6],"ItemType",data[7],"ItemType",data[8],"ItemType",data[9]);
        }


        System.out.println(sql);

        try {
            Statement sta = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = sta.executeQuery(sql);

            rs.last();
            int count = rs.getRow();
            rs.beforeFirst();
            ItemInformation[] itemInformation = new ItemInformation[count];

            int i = 0;
            while (rs.next()) {
                itemInformation[i] = new ItemInformation();
                itemInformation[i].setItemID(rs.getString("ItemID"));
                itemInformation[i].setItemName(rs.getString("ItemName"));
                itemInformation[i].setItemQuantity(rs.getInt("ItemQuantity"));
                itemInformation[i].setItemPrice(rs.getDouble("ItemPrice"));
                itemInformation[i].setItemType(rs.getString("ItemType"));
                itemInformation[i].setItemDescription(rs.getString("ItemDescription"));
                itemInformation[i].setCreateTime(rs.getTimestamp("CreateTime"));
                i++;
            }
            return itemInformation;
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return new ItemInformation[0];
    }

    public static ItemInformation[] QueryItemNameInformation(String data) {

        Connection con = getConnection();

//        String sql = "select * from item_information where " + dataName + "=" + "\"" + data + "\";";

        String sql = "select * from item_information where `ItemName` like '%" + data + "%'";
        System.out.println(sql);


        System.out.println(sql);

        try {
            Statement sta = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = sta.executeQuery(sql);

            rs.last();
            int count = rs.getRow();
            rs.beforeFirst();
            ItemInformation[] itemInformation = new ItemInformation[count];

            int i = 0;
            while (rs.next()) {
                itemInformation[i] = new ItemInformation();
                itemInformation[i].setItemID(rs.getString("ItemID"));
                System.out.println(rs.getString("ItemID") + "  " + i);
                itemInformation[i].setItemName(rs.getString("ItemName"));
                itemInformation[i].setItemQuantity(rs.getInt("ItemQuantity"));
                itemInformation[i].setItemPrice(rs.getDouble("ItemPrice"));
                itemInformation[i].setItemType(rs.getString("ItemType"));
                itemInformation[i].setItemDescription(rs.getString("ItemDescription"));
                itemInformation[i].setCreateTime(rs.getTimestamp("CreateTime"));
                i++;
            }
            return itemInformation;
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return new ItemInformation[0];
    }


    public static String QueryItemPicture(String itemID) {
        Connection con = getConnection();

//        String sql = "select Picture from item_picture where ItemID=\'" + itemID + "\';";
        String sql = String.format("select Picture from item_picture where `ItemID`= '%s'", itemID);
        System.out.println(sql);

        try {
            Statement stmt = null;
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            rs.next();
            return rs.getString("Picture");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }


    }


    public static ItemInformation[] QueryItemInformation() {
        Connection con = getConnection();

//        String sql = "select * from item_information where " + dataName + "=" + "\"" + data + "\";";

        String sql = String.format("select * from item_information");


        System.out.println(sql);

        try {
            Statement sta = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = sta.executeQuery(sql);

            rs.last();
            int count = rs.getRow();
            rs.beforeFirst();
            ItemInformation[] itemInformation = new ItemInformation[count];

            int i = 0;
            while (rs.next()) {
                itemInformation[i] = new ItemInformation();
                itemInformation[i].setItemID(rs.getString("ItemID"));
                itemInformation[i].setItemName(rs.getString("ItemName"));
                itemInformation[i].setItemQuantity(rs.getInt("ItemQuantity"));
                itemInformation[i].setItemPrice(rs.getDouble("ItemPrice"));
                itemInformation[i].setItemType(rs.getString("ItemType"));
                itemInformation[i].setItemDescription(rs.getString("ItemDescription"));
                itemInformation[i].setCreateTime(rs.getTimestamp("CreateTime"));
                i++;
            }
            return itemInformation;
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return new ItemInformation[0];
    }

    public static AccountInformation QueryAccountInformation(String PhoneNumber) {

        Connection con = getConnection();

//        String sql = "select * from account_information where " + dataName + "=" + "\"" + data + "\";";

        String sql = String.format("select * from account_information where `%s` = '%s'", "PhoneNumber", PhoneNumber);
        System.out.println(sql);

        try {
            Statement sta = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = sta.executeQuery(sql);

            rs.last();
            int count = rs.getRow();
            rs.beforeFirst();
            AccountInformation accountIf = new AccountInformation();
            while (rs.next()) {

                accountIf.setAccountName(rs.getString("AccountName"));
                accountIf.setPassword(rs.getString("Password"));
                accountIf.setPhoneNumber(rs.getString("PhoneNumber"));
                accountIf.setRealName(rs.getString("RealName"));
                accountIf.setDeposit(rs.getDouble("Deposit"));
                accountIf.setCreateTime(rs.getTimestamp("CreateTime"));
            }

            return accountIf;
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }

    public static void main(String[] args) {

    }

}
